import os
import sqlite3
import json
import secrets
import uuid
from datetime import datetime, timedelta
from functools import wraps
from werkzeug.security import generate_password_hash, check_password_hash
from flask import Flask, request, jsonify, g, session
from flask_cors import CORS

# 初始化 Flask 应用
app = Flask(__name__)
# 从环境变量获取密钥，如果没有则生成随机密钥
app.secret_key = os.environ.get('SECRET_KEY', secrets.token_hex(16))

# 配置会话相关设置
app.config['SESSION_COOKIE_SECURE'] = False  # 开发环境不需要HTTPS
app.config['SESSION_COOKIE_HTTPONLY'] = True
app.config['SESSION_COOKIE_SAMESITE'] = 'Lax'  # 允许从外部链接访问
app.config['PERMANENT_SESSION_LIFETIME'] = timedelta(days=30)  # 设置会话有效期为30天

# 允许所有前端源或指定源
CORS(app, resources={r"/api/*": {"origins": os.environ.get('CORS_ORIGIN', 'http://localhost:5173')}}, supports_credentials=True)

# 数据库配置
DATABASE = 'todos.db'

# 获取数据库连接
def get_db():
    db = getattr(g, '_database', None)
    if db is None:
        db = g._database = sqlite3.connect(DATABASE)
        db.row_factory = sqlite3.Row
    return db

# 关闭数据库连接
@app.teardown_appcontext
def close_connection(exception):
    db = getattr(g, '_database', None)
    if db is not None:
        db.close()

# 初始化数据库
def init_db():
    with app.app_context():
        db = get_db()
        cursor = db.cursor()

        # 检查todos表是否存在
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='todos'")
        todos_exists = cursor.fetchone() is not None

        # 如果todos表存在，检查是否有user_id列
        if todos_exists:
            cursor.execute("PRAGMA table_info(todos)")
            columns = cursor.fetchall()
            has_user_id = any(col['name'] == 'user_id' for col in columns)

            # 如果没有user_id列，需要重建表
            if not has_user_id:
                print("需要更新todos表结构，添加user_id列")
                # 重命名旧表
                cursor.execute("ALTER TABLE todos RENAME TO todos_old")

                # 创建新表
                cursor.execute('''
                CREATE TABLE todos (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    user_id INTEGER NOT NULL,
                    title TEXT NOT NULL,
                    description TEXT,
                    priority INTEGER NOT NULL DEFAULT 2,
                    due_date TIMESTAMP,
                    completed BOOLEAN NOT NULL DEFAULT 0,
                    created_at TIMESTAMP NOT NULL,
                    updated_at TIMESTAMP NOT NULL,
                    FOREIGN KEY (user_id) REFERENCES users (id)
                )
                ''')

                # 获取第一个用户的ID作为默认值
                cursor.execute("SELECT id FROM users LIMIT 1")
                user_row = cursor.fetchone()
                default_user_id = user_row['id'] if user_row else 1

                # 迁移数据，为所有现有的todo设置默认用户ID
                cursor.execute('''
                INSERT INTO todos (id, user_id, title, description, priority, due_date, completed, created_at, updated_at)
                SELECT id, ?, title, description, priority, due_date, completed, created_at, updated_at
                FROM todos_old
                ''', (default_user_id,))

                # 删除旧表
                cursor.execute("DROP TABLE todos_old")

                db.commit()
                print("todos表结构已更新，添加了user_id列")

        # 创建用户表
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE NOT NULL,
            password TEXT NOT NULL,
            email TEXT UNIQUE,
            created_at TIMESTAMP NOT NULL,
            last_login TIMESTAMP,
            token TEXT,
            token_expiry TIMESTAMP
        )
        ''')

        # 创建待办事项表，添加用户ID外键
        if not todos_exists:
            cursor.execute('''
            CREATE TABLE IF NOT EXISTS todos (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL,
                title TEXT NOT NULL,
                description TEXT,
                priority INTEGER NOT NULL DEFAULT 2,
                due_date TIMESTAMP,
                completed BOOLEAN NOT NULL DEFAULT 0,
                created_at TIMESTAMP NOT NULL,
                updated_at TIMESTAMP NOT NULL,
                FOREIGN KEY (user_id) REFERENCES users (id)
            )
            ''')

        db.commit()
        print("数据库初始化成功")

# 格式化 Todo 对象
def format_todo(todo):
    return {
        'id': todo['id'],
        'title': todo['title'],
        'description': todo['description'] if 'description' in todo else '',
        'priority': todo['priority'],
        'due_date': todo['due_date'] if 'due_date' in todo else None,
        'completed': bool(todo['completed']),
        'created_at': todo['created_at'],
        'updated_at': todo['updated_at']
    }

# 格式化用户对象
def format_user(user):
    return {
        'id': user['id'],
        'username': user['username'],
        'email': user['email'] if 'email' in user else None,
        'created_at': user['created_at'],
        'last_login': user['last_login'] if 'last_login' in user else None,
        'token': user['token'] if 'token' in user else None
    }

# 验证用户是否登录的装饰器
def login_required(f):
    @wraps(f)
    def decorated_function(*args, **kwargs):
        # 首先检查会话
        if 'user_id' in session:
            # 刷新会话过期时间
            session.permanent = True
            session.modified = True  # 确保会话被保存
            return f(*args, **kwargs)

        # 如果没有会话，检查请求头中的token
        auth_header = request.headers.get('Authorization')
        if auth_header and auth_header.startswith('Bearer '):
            token = auth_header.split(' ')[1]

            # 验证token
            db = get_db()
            cursor = db.cursor()
            now = datetime.now().isoformat()

            # 查找具有有效token的用户
            cursor.execute('SELECT * FROM users WHERE token = ? AND datetime(token_expiry) > datetime(?)', (token, now))
            user = cursor.fetchone()

            if user:
                # 刷新token过期时间
                expiry = (datetime.now() + timedelta(days=30)).isoformat()
                cursor.execute('UPDATE users SET token_expiry = ?, last_login = ? WHERE id = ?',
                               (expiry, now, user['id']))
                db.commit()

                # 设置会话
                session['user_id'] = user['id']
                session.permanent = True
                session.modified = True

                return f(*args, **kwargs)

        # 如果没有有效的会话或token，返回未授权错误
        return jsonify({'error': 'Unauthorized', 'message': '请先登录'}), 401
    return decorated_function

# 在每次请求前刷新会话
@app.before_request
def refresh_session():
    if 'user_id' in session:
        session.permanent = True
        session.modified = True

# 获取当前登录用户
def get_current_user():
    # 首先检查会话
    if 'user_id' in session:
        db = get_db()
        cursor = db.cursor()
        cursor.execute('SELECT * FROM users WHERE id = ?', (session['user_id'],))
        user = cursor.fetchone()

        if user:
            return dict(user)

    # 如果没有会话，检查请求头中的token
    auth_header = request.headers.get('Authorization')
    if auth_header and auth_header.startswith('Bearer '):
        token = auth_header.split(' ')[1]

        # 验证token
        db = get_db()
        cursor = db.cursor()
        now = datetime.now().isoformat()

        # 查找具有有效token的用户
        cursor.execute('SELECT * FROM users WHERE token = ? AND datetime(token_expiry) > datetime(?)', (token, now))
        user = cursor.fetchone()

        if user:
            # 刷新token过期时间
            expiry = (datetime.now() + timedelta(days=30)).isoformat()
            cursor.execute('UPDATE users SET token_expiry = ?, last_login = ? WHERE id = ?',
                           (expiry, now, user['id']))
            db.commit()

            # 设置会话
            session['user_id'] = user['id']
            session.permanent = True
            session.modified = True

            return dict(user)

    return None

# 认证相关的API路由
@app.route('/api/auth/register', methods=['POST'])
def register():
    if not request.json:
        return jsonify({'error': 'Invalid request'}), 400

    username = request.json.get('username')
    password = request.json.get('password')
    email = request.json.get('email')

    if not username or not password:
        return jsonify({'error': 'Username and password are required'}), 400

    db = get_db()
    cursor = db.cursor()

    # 检查用户名是否已存在
    cursor.execute('SELECT * FROM users WHERE username = ?', (username,))
    if cursor.fetchone():
        return jsonify({'error': 'Username already exists'}), 409

    # 检查邮箱是否已存在（如果提供了邮箱）
    if email:
        cursor.execute('SELECT * FROM users WHERE email = ?', (email,))
        if cursor.fetchone():
            return jsonify({'error': 'Email already exists'}), 409

    # 生成一个随机的token和过期时间（30天后）
    token = str(uuid.uuid4())
    now = datetime.now()
    expiry = (now + timedelta(days=30)).isoformat()
    now_iso = now.isoformat()

    # 创建新用户
    hashed_password = generate_password_hash(password)

    cursor.execute(
        'INSERT INTO users (username, password, email, created_at, last_login, token, token_expiry) VALUES (?, ?, ?, ?, ?, ?, ?)',
        (username, hashed_password, email, now_iso, now_iso, token, expiry)
    )
    db.commit()

    # 获取新创建的用户
    user_id = cursor.lastrowid
    cursor.execute('SELECT * FROM users WHERE id = ?', (user_id,))
    user = cursor.fetchone()

    # 设置会话并使其永久化
    session.permanent = True
    session['user_id'] = user_id
    session.modified = True  # 确保会话被保存

    return jsonify({
        'message': 'User registered successfully',
        'user': format_user(dict(user))
    }), 201

@app.route('/api/auth/login', methods=['POST'])
def login():
    if not request.json:
        return jsonify({'error': 'Invalid request'}), 400

    username = request.json.get('username')
    password = request.json.get('password')

    if not username or not password:
        return jsonify({'error': 'Username and password are required'}), 400

    db = get_db()
    cursor = db.cursor()

    # 查找用户
    cursor.execute('SELECT * FROM users WHERE username = ?', (username,))
    user = cursor.fetchone()

    if not user or not check_password_hash(user['password'], password):
        return jsonify({'error': 'Invalid username or password'}), 401

    # 生成一个随机的token和过期时间（30天后）
    token = str(uuid.uuid4())
    now = datetime.now()
    expiry = (now + timedelta(days=30)).isoformat()
    now_iso = now.isoformat()

    # 更新数据库中的用户信息
    cursor.execute('UPDATE users SET last_login = ?, token = ?, token_expiry = ? WHERE id = ?',
                   (now_iso, token, expiry, user['id']))
    db.commit()

    # 重新获取用户信息包含新token
    cursor.execute('SELECT * FROM users WHERE id = ?', (user['id'],))
    user = cursor.fetchone()

    # 设置会话并使其永久化
    session.permanent = True
    session['user_id'] = user['id']
    session.modified = True  # 确保会话被保存

    return jsonify({
        'message': 'Login successful',
        'user': format_user(dict(user))
    })

@app.route('/api/auth/logout', methods=['POST'])
def logout():
    session.pop('user_id', None)
    return jsonify({'message': 'Logout successful'})

@app.route('/api/auth/user', methods=['GET'])
def get_user():
    user = get_current_user()
    if not user:
        return jsonify({'error': 'Not logged in'}), 401

    # 如果用户已登录，刷新会话过期时间
    if 'user_id' in session:
        # 确保会话是永久的
        session.permanent = True
        session.modified = True  # 确保会话被保存

        # 更新最后登录时间
        db = get_db()
        now = datetime.now().isoformat()
        db.execute('UPDATE users SET last_login = ? WHERE id = ?', (now, session['user_id']))
        db.commit()

    return jsonify(format_user(user))

# Todo相关的API路由
@app.route('/api/todos', methods=['GET'])
@login_required
def get_todos():
    db = get_db()
    cursor = db.cursor()

    # 获取当前用户ID
    user_id = session['user_id']

    # 获取查询参数
    completed = request.args.get('completed')
    priority = request.args.get('priority')
    search = request.args.get('search')
    sort_by = request.args.get('sort_by', 'priority')  # 默认按优先级排序
    sort_order = request.args.get('sort_order', 'asc')  # 默认升序

    # 构建查询
    query = 'SELECT * FROM todos WHERE user_id = ?'
    params = [user_id]

    if completed is not None:
        query += ' AND completed = ?'
        params.append(int(completed == 'true'))

    if priority is not None:
        query += ' AND priority = ?'
        params.append(int(priority))

    if search is not None and search.strip():
        query += ' AND (title LIKE ? OR description LIKE ?)'
        search_param = f'%{search}%'
        params.append(search_param)
        params.append(search_param)

    # 添加排序
    if sort_by == 'priority':
        query += ' ORDER BY priority'
    elif sort_by == 'due_date':
        query += ' ORDER BY due_date'
    elif sort_by == 'created_at':
        query += ' ORDER BY created_at'
    else:
        query += ' ORDER BY id'

    if sort_order.lower() == 'desc':
        query += ' DESC'
    else:
        query += ' ASC'

    cursor.execute(query, params)
    todos = [format_todo(dict(row)) for row in cursor.fetchall()]
    return jsonify(todos)

@app.route('/api/todos/<int:todo_id>', methods=['GET'])
@login_required
def get_todo(todo_id):
    user_id = session['user_id']
    db = get_db()
    cursor = db.cursor()
    cursor.execute('SELECT * FROM todos WHERE id = ? AND user_id = ?', (todo_id, user_id))
    todo = cursor.fetchone()

    if todo is None:
        return jsonify({'error': 'Todo not found'}), 404

    return jsonify(format_todo(dict(todo)))

@app.route('/api/todos', methods=['POST'])
@login_required
def create_todo():
    if not request.json or 'title' not in request.json:
        return jsonify({'error': 'Title is required'}), 400

    user_id = session['user_id']
    now = datetime.now().isoformat()
    title = request.json['title']
    description = request.json.get('description', '')
    priority = request.json.get('priority', 2)  # 默认中等优先级
    due_date = request.json.get('due_date')
    completed = request.json.get('completed', False)

    db = get_db()
    cursor = db.cursor()
    cursor.execute(
        'INSERT INTO todos (user_id, title, description, priority, due_date, completed, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
        (user_id, title, description, priority, due_date, completed, now, now)
    )
    db.commit()

    # 获取新创建的 todo
    todo_id = cursor.lastrowid
    cursor.execute('SELECT * FROM todos WHERE id = ?', (todo_id,))
    todo = cursor.fetchone()

    return jsonify(format_todo(dict(todo))), 201

@app.route('/api/todos/<int:todo_id>', methods=['PUT'])
@login_required
def update_todo(todo_id):
    user_id = session['user_id']
    db = get_db()
    cursor = db.cursor()
    cursor.execute('SELECT * FROM todos WHERE id = ? AND user_id = ?', (todo_id, user_id))
    todo = cursor.fetchone()

    if todo is None:
        return jsonify({'error': 'Todo not found'}), 404

    if not request.json:
        return jsonify({'error': 'No data provided'}), 400

    title = request.json.get('title', todo['title'])
    description = request.json.get('description', todo['description'] if 'description' in todo else '')
    priority = request.json.get('priority', todo['priority'])
    due_date = request.json.get('due_date', todo['due_date'] if 'due_date' in todo else None)
    completed = request.json.get('completed', todo['completed'])
    now = datetime.now().isoformat()

    cursor.execute(
        'UPDATE todos SET title = ?, description = ?, priority = ?, due_date = ?, completed = ?, updated_at = ? WHERE id = ? AND user_id = ?',
        (title, description, priority, due_date, completed, now, todo_id, user_id)
    )
    db.commit()

    # 获取更新后的 todo
    cursor.execute('SELECT * FROM todos WHERE id = ?', (todo_id,))
    updated_todo = cursor.fetchone()

    return jsonify(format_todo(dict(updated_todo)))

@app.route('/api/todos/<int:todo_id>', methods=['DELETE'])
@login_required
def delete_todo(todo_id):
    user_id = session['user_id']
    db = get_db()
    cursor = db.cursor()
    cursor.execute('SELECT * FROM todos WHERE id = ? AND user_id = ?', (todo_id, user_id))
    todo = cursor.fetchone()

    if todo is None:
        return jsonify({'error': 'Todo not found'}), 404

    cursor.execute('DELETE FROM todos WHERE id = ? AND user_id = ?', (todo_id, user_id))
    db.commit()

    return jsonify({'message': 'Todo deleted successfully'})

# 批量操作 API
@app.route('/api/todos/batch', methods=['POST'])
@login_required
def batch_operations():
    if not request.json or 'operation' not in request.json:
        return jsonify({'error': 'Operation is required'}), 400

    user_id = session['user_id']
    operation = request.json['operation']
    todo_ids = request.json.get('ids', [])

    if not todo_ids:
        return jsonify({'error': 'No todo IDs provided'}), 400

    db = get_db()
    cursor = db.cursor()

    # 验证所有的todo都属于当前用户
    placeholders = ', '.join(['?' for _ in todo_ids])
    cursor.execute(f'SELECT COUNT(*) as count FROM todos WHERE id IN ({placeholders}) AND user_id = ?', todo_ids + [user_id])
    result = cursor.fetchone()
    if result['count'] != len(todo_ids):
        return jsonify({'error': 'Some todos do not belong to the current user'}), 403

    if operation == 'delete':
        # 批量删除
        placeholders = ', '.join(['?' for _ in todo_ids])
        cursor.execute(f'DELETE FROM todos WHERE id IN ({placeholders}) AND user_id = ?', todo_ids + [user_id])
        db.commit()
        return jsonify({'message': f'{len(todo_ids)} todos deleted successfully'})

    elif operation == 'complete':
        # 批量标记为已完成
        now = datetime.now().isoformat()
        placeholders = ', '.join(['?' for _ in todo_ids])
        cursor.execute(f'UPDATE todos SET completed = 1, updated_at = ? WHERE id IN ({placeholders}) AND user_id = ?', [now] + todo_ids + [user_id])
        db.commit()
        return jsonify({'message': f'{len(todo_ids)} todos marked as completed'})

    elif operation == 'uncomplete':
        # 批量标记为未完成
        now = datetime.now().isoformat()
        placeholders = ', '.join(['?' for _ in todo_ids])
        cursor.execute(f'UPDATE todos SET completed = 0, updated_at = ? WHERE id IN ({placeholders}) AND user_id = ?', [now] + todo_ids + [user_id])
        db.commit()
        return jsonify({'message': f'{len(todo_ids)} todos marked as uncompleted'})

    elif operation == 'set_priority':
        # 批量设置优先级
        if 'priority' not in request.json:
            return jsonify({'error': 'Priority is required for set_priority operation'}), 400

        priority = request.json['priority']
        now = datetime.now().isoformat()
        placeholders = ', '.join(['?' for _ in todo_ids])
        cursor.execute(f'UPDATE todos SET priority = ?, updated_at = ? WHERE id IN ({placeholders}) AND user_id = ?', [priority, now] + todo_ids + [user_id])
        db.commit()
        return jsonify({'message': f'Priority set to {priority} for {len(todo_ids)} todos'})

    else:
        return jsonify({'error': 'Invalid operation'}), 400

# 获取统计信息
@app.route('/api/todos/stats', methods=['GET'])
@login_required
def get_stats():
    user_id = session['user_id']
    db = get_db()
    cursor = db.cursor()

    # 总数量
    cursor.execute('SELECT COUNT(*) as total FROM todos WHERE user_id = ?', (user_id,))
    total = cursor.fetchone()['total']

    # 已完成数量
    cursor.execute('SELECT COUNT(*) as completed FROM todos WHERE completed = 1 AND user_id = ?', (user_id,))
    completed_count = cursor.fetchone()['completed']

    # 按优先级统计
    cursor.execute('SELECT priority, COUNT(*) as count FROM todos WHERE user_id = ? GROUP BY priority', (user_id,))
    priority_stats = {row['priority']: row['count'] for row in cursor.fetchall()}

    # 近期到期的任务
    now = datetime.now().isoformat()
    cursor.execute('SELECT COUNT(*) as count FROM todos WHERE user_id = ? AND due_date IS NOT NULL AND due_date > ? AND due_date < datetime(?, "+7 days") AND completed = 0', (user_id, now, now))
    upcoming_due = cursor.fetchone()['count']

    return jsonify({
        'total': total,
        'completed': completed_count,
        'pending': total - completed_count,
        'completion_rate': round(completed_count / total * 100, 2) if total > 0 else 0,
        'priority_stats': priority_stats,
        'upcoming_due': upcoming_due
    })

if __name__ == '__main__':
    # 初始化数据库
    init_db()
    # 启动应用
    app.run(debug=True, host='0.0.0.0', port=8080)
